package db.jdbc;

import db.h2.H2DbHelper;

import javax.sql.rowset.serial.SerialBlob;
import java.io.File;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Path;
import java.sql.*;

public class StudentExercise {
    Connection connection;

    // 构造函数，传入数据库连接
    public StudentExercise(Connection connection) {
        this.connection = connection;
    }

    // 用给定的no学号，name姓名，age年龄，birthday出生日期，插入到表中
    public void insert(String no, String name, int age, Date birthday) throws SQLException {
    }

    // 删除指定id的记录
    public void delete(int id) throws SQLException {
    }

    // 修改表数据，按id修改数据，类似insert
    public void update(int id, String no, String name, int age, Date birthday) throws SQLException {
        String sql = "update Student set No=?, Name=?, Age=?, Birthday=? where Id=?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setString(1, no);
            pstmt.setString(2, name);
            pstmt.setInt(3, age);
            pstmt.setDate(4, birthday);
            pstmt.setInt(5, id);
            pstmt.executeUpdate();
        }
    }

    // 按 id 查询学生信息，把查到的数据保存到Student对象中，
    // 参考：StudentDAO 的 createStudent 方法
    public JdbcStudent findById(int id) throws SQLException {
        JdbcStudent student = new JdbcStudent();
        String sql = "select * from Student where Id=?";
        try (PreparedStatement pstmt = connection.prepareStatement(sql)) {
            pstmt.setInt(1, id);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                student.setId(rs.getInt("Id"));
                student.setNo(rs.getString("No"));
                student.setName(rs.getString("Name"));
                student.setAge(rs.getInt("Age"));
                student.setBirthday(rs.getDate("Birthday"));
            }
        }
        return student;
    }

    public static void insertPhoto() throws IOException, SQLException {
        // 插入数据时个不好编写测试，各位同学完成后，可以使用同时插入图片，这
        // StudentView 验证
        // 方法：
        // 1. 读取图片文件到 byte[]
        //String fn = StudentExercise.class.getClassLoader().getResource("photo.jpg").getFile();
        String fn = "f:\\ss.jpg";
        File photoFile = new File(fn);
        byte[] data = Files.readAllBytes(Path.of(photoFile.getAbsolutePath()));
        // 2. 创建 SerialBlob
        SerialBlob photoBlob = new SerialBlob(data);
        // 3. 更新数据库表
        String sql = "update Student set Photo=? where Id=?";
        H2DbHelper dbHelper = new H2DbHelper();
        try(PreparedStatement pstmt = dbHelper.getConnection().prepareStatement(sql)) {
            pstmt.setBlob(1, photoBlob);
            pstmt.setInt(2, 158);
            pstmt.execute();
        }
    }

    public static void main(String[] args) throws SQLException, IOException {
        insertPhoto();
    }
}
